﻿using MySql.Data.MySqlClient;
using System.Data;

    /// <summary>
    /// 其实MySQL的DLL已经包括了此Helper
    /// </summary>
    public class MySQLHelper
    {
    //连接到阿里云RDS,已设置IP白名单为全部(即客户端外网直连).
        //private static string connStr2 = "server=rdsll3g63330kwrz1f74o.mysql.rds.aliyuncs.com;uid=crazyq_test;pwd=12345a;persistsecurityinfo=True;database=crazyqtest";
    private static string connStr = "server=localhost;uid=root;pwd=root;persistsecurityinfo=True;database=mycompany";
    private static DataTable ExecuteTable(string txt, CommandType ct, params MySqlParameter[] param)
        {
            using (MySqlDataAdapter sda = new MySqlDataAdapter(txt, connStr))
            {
                sda.SelectCommand.CommandType = ct;
                sda.SelectCommand.Parameters.AddRange(param);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static DataTable ExecuteTableSQL(string sql, params MySqlParameter[] param)
        {
            return ExecuteTable(sql, CommandType.Text, param);
        }


        public static DataTable ExecuteTableSP(string sp, params MySqlParameter[] param)
        {
            return ExecuteTable(sp, CommandType.StoredProcedure, param);
        }

        /// <summary>
        /// 执行增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, params MySqlParameter[] param)
        {
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddRange(param);
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }


        /// <summary>
        /// 执行查询，返回首行首列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params MySqlParameter[] param)
        {
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddRange(param);
                    conn.Open();

                    return cmd.ExecuteScalar();
                }
            }
        }
    }

